package com.maixy.dailydemo.export;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;

/**
 * @ClassName GetSQL
 * @Description TODO
 * @Date 2021/8/12 13:29
 * @Author NINGMEI
 */
@Slf4j
public class GetSQL {
    public static void main(String[] args) throws IOException {
        //loadScoreInfo("C:\\Users\\NINGMEI\\Desktop\\随便放\\客户列表.xls");
        loadScoreInfo("C:\\Users\\NINGMEI\\Desktop\\随便放\\客户销售经理匹配.xls");
    }
    public static void loadScoreInfo(String xlsPath) throws IOException {
        FileInputStream fileIn = new FileInputStream(xlsPath);
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new HSSFWorkbook(fileIn);
        //获取Excel文档中的第一个表单
        Sheet sht0 = wb0.getSheetAt(0);
        //对Sheet中的每一行进行迭代
        for (Row r : sht0) {
            //如果当前行的行号（从0开始）未达到2（第三行）则从新循环
            if(r.getRowNum() < 1) {
                continue;
            }
            Cell cell0 = r.getCell(0);
            cell0.setCellType(CellType.STRING);
            String value0 =  cell0.getStringCellValue();

            Cell cell1 = r.getCell(1);
            cell1.setCellType(CellType.STRING);
            String value1 =  cell1.getStringCellValue();

            Cell cell2 = r.getCell(2);
            cell1.setCellType(CellType.STRING);
            String value2 =  cell2.getStringCellValue();

            Cell cell3 = r.getCell(3);
            cell1.setCellType(CellType.STRING);
            String value3 =  cell3.getStringCellValue();

            Cell cell4 = r.getCell(4);
            cell1.setCellType(CellType.STRING);
            String value4 =  cell4.getStringCellValue();

            Cell cell5 = r.getCell(5);
            cell5.setCellType(CellType.STRING);
            String value5 =  cell5.getStringCellValue();

            Cell cell6 = r.getCell(6);
            cell6.setCellType(CellType.STRING);
            String value6 =  cell6.getStringCellValue();
            //取出当前行第1个单元格数据，并封装在info实体stuName属性上
            //System.out.println(r.getCell(0).getStringCellValue());
            //System.out.println(r.getCell(1).getStringCellValue());
            String sql = "update jxc_down_customer set simple_name='" + value1 + "', province='" + value2 + "', city='" + value3 + "', district='" + value4
                    + "', mobile='" + value5 + "',user_id=(select user_id from sys_user where name='" + value6 + "' limit 1) where jxc_down_customer_id=" + value0 +";";
            System.out.println(sql);
            //WriteStringToFile2("C:\\Users\\NINGMEI\\Desktop\\随便放\\客户列表.txt", sql + "\n");
            WriteStringToFile2("C:\\Users\\NINGMEI\\Desktop\\随便放\\客户销售经理匹配.txt", sql + "\n");
        }
        fileIn.close();
    }

    public static void WriteStringToFile2(String filePath, String str) {
        try {
            FileWriter fw = new FileWriter(filePath, true);
            BufferedWriter bw = new BufferedWriter(fw);
            bw.append(str);
            //bw.write("abc\r\n ");// 往已有的文件上添加字符串
            //bw.write("def\r\n ");
            //bw.write("hijk ");
            bw.close();
            fw.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
